package com.ib.review.base.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class CompareExcelExporter {
    public static byte[] exportTablesToExcel(Map<String, List<List<Map<String, Object>>>> jsonData) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            // 遍历每个Sheet
            for (Map.Entry<String, List<List<Map<String, Object>>>> entry : jsonData.entrySet()) {
                String sheetName = entry.getKey();
                List<List<Map<String, Object>>> sheetData = entry.getValue();
                // 创建Sheet（名称截断至31字符）
                Sheet sheet = workbook.createSheet(truncateSheetName(sheetName));

                // 处理表头和数据
                processSheet(workbook, sheet, sheetData);
            }

            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }

    private static void processSheet(Workbook workbook, Sheet sheet, List<List<Map<String, Object>>> data) {
        CellStyle headerStyle = createHeaderStyle(workbook);
        CellStyle dataStyle = createDataStyle(workbook);

        // 存储所有合并区域
        List<CellRangeAddress> mergedRegions = new ArrayList<>();

        // 第一遍：创建单元格和合并区域
        for (int rowNum = 0; rowNum < data.size(); rowNum++) {
            List<Map<String, Object>> rowData = data.get(rowNum);
            Row row = sheet.createRow(rowNum);

            for (int colNum = 0; colNum < rowData.size(); colNum++) {
                Map<String, Object> cellData = rowData.get(colNum);
                if (isEmptyCell(cellData)) continue;

                Cell cell = row.createCell(colNum);
                setCellValue(cell, cellData.get("value"));
                cell.setCellStyle("TH".equals(cellData.get("tagName")) ? headerStyle : dataStyle);

                // 记录合并区域
                int rowSpan = Integer.parseInt(cellData.getOrDefault("rowSpan", "1").toString());
                int colSpan = Integer.parseInt(cellData.getOrDefault("colSpan", "1").toString());
                if (rowSpan > 1 || colSpan > 1) {
                    mergedRegions.add(new CellRangeAddress(
                            rowNum, rowNum + rowSpan - 1,
                            colNum, colNum + colSpan - 1
                    ));
                }
            }
        }

        // 第二遍：处理合并区域的边框（关键改进）
        for (CellRangeAddress region : mergedRegions) {
            sheet.addMergedRegion(region);
            enforceBordersForMergedRegion(sheet, region);
        }

        // 调整列宽
        for (int i = 0; i < data.get(0).size(); i++) {
            sheet.autoSizeColumn(i);
        }
    }

    // 强制设置合并区域的边框
    private static void enforceBordersForMergedRegion(Sheet sheet, CellRangeAddress region) {
        Workbook workbook = sheet.getWorkbook();

        // 创建带全边框的样式
        CellStyle borderStyle = workbook.createCellStyle();
        borderStyle.setBorderTop(BorderStyle.THIN);
        borderStyle.setBorderBottom(BorderStyle.THIN);
        borderStyle.setBorderLeft(BorderStyle.THIN);
        borderStyle.setBorderRight(BorderStyle.THIN);

        // 应用样式到合并区域的四边
        for (int r = region.getFirstRow(); r <= region.getLastRow(); r++) {
            Row row = sheet.getRow(r) != null ? sheet.getRow(r) : sheet.createRow(r);

            // 左侧单元格
            Cell leftCell = row.getCell(region.getFirstColumn());
            if (leftCell == null) leftCell = row.createCell(region.getFirstColumn());
            CellStyle leftStyle = workbook.createCellStyle();
            leftStyle.cloneStyleFrom(leftCell.getCellStyle());
            leftStyle.setBorderLeft(BorderStyle.THIN);
            leftCell.setCellStyle(leftStyle);

            // 右侧单元格
            Cell rightCell = row.getCell(region.getLastColumn());
            if (rightCell == null) rightCell = row.createCell(region.getLastColumn());
            CellStyle rightStyle = workbook.createCellStyle();
            rightStyle.cloneStyleFrom(rightCell.getCellStyle());
            rightStyle.setBorderRight(BorderStyle.THIN);
            rightCell.setCellStyle(rightStyle);
        }

        for (int c = region.getFirstColumn(); c <= region.getLastColumn(); c++) {
            // 上方单元格
            if (region.getFirstRow() > 0) {
                Row topRow = sheet.getRow(region.getFirstRow() - 1);
                if (topRow != null) {
                    Cell topCell = topRow.getCell(c);
                    if (topCell != null) {
                        CellStyle topStyle = workbook.createCellStyle();
                        topStyle.cloneStyleFrom(topCell.getCellStyle());
                        topStyle.setBorderBottom(BorderStyle.THIN);
                        topCell.setCellStyle(topStyle);
                    }
                }
            }

            // 下方单元格
            Row bottomRow = sheet.getRow(region.getLastRow());
            if (bottomRow != null) {
                Cell bottomCell = bottomRow.getCell(c);
                if (bottomCell != null) {
                    CellStyle bottomStyle = workbook.createCellStyle();
                    bottomStyle.cloneStyleFrom(bottomCell.getCellStyle());
                    bottomStyle.setBorderBottom(BorderStyle.THIN);
                    bottomCell.setCellStyle(bottomStyle);
                }
            }
        }
    }

    // 创建表头样式（去掉背景色）
    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 添加边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        return style;
    }

    // 创建数据样式（带边框）
    private static CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 添加边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        return style;
    }

    // 其他辅助方法保持不变
    private static boolean isEmptyCell(Map<String, Object> cellData) {
        return "EMPTY".equals(cellData.get("tagName")) ||
                cellData.get("value") == null ||
                "".equals(cellData.get("value"));
    }

    private static void setCellValue(Cell cell, Object value) {
        if (value == null) {
            cell.setCellValue("");
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else {
            cell.setCellValue(value.toString());
        }
    }

    private static String truncateSheetName(String name) {
        return name.length() > 31 ? name.substring(0, 31) : name;
    }
}
